{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 使用LLM模型构建一个Text2SQL的应用程序\n",
    "\n",
    "## 1.准备环境变量.env"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "f3d45451790b41f5"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "API_BASE_URL: https://cognihubemp.baystoneai.com/cognihub/service\n"
     ]
    }
   ],
   "source": [
    "from dotenv import load_dotenv\n",
    "import os\n",
    "\n",
    "# 加载 .env 文件\n",
    "load_dotenv()\n",
    "\n",
    "# 读取环境变量\n",
    "API_BASE_URL = os.getenv(\"API_BASE_URL\")\n",
    "API_KEY = os.getenv(\"API_KEY\")\n",
    "langsmith_api_key = os.getenv(\"LANGSMITH_API_KEY\")\n",
    "print(\"API_BASE_URL:\", API_BASE_URL)"
   ],
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:08:17.450108Z",
     "start_time": "2024-11-08T06:08:17.418700Z"
    }
   },
   "id": "initial_id",
   "execution_count": 17
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 2.准备LLM模型"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "bf94d204e8e7688d"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "from langchain_openai import ChatOpenAI\n",
    "\n",
    "CHAT_MODEL = \"qwen2.5-instruct\"\n",
    "llm = ChatOpenAI(\n",
    "    model=CHAT_MODEL,\n",
    "    base_url=f'{API_BASE_URL}/v1',\n",
    "    api_key=API_KEY,\n",
    "    temperature=0,\n",
    "    streaming=True\n",
    ")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:08:17.652554Z",
     "start_time": "2024-11-08T06:08:17.478800Z"
    }
   },
   "id": "5cac53f2db23d159",
   "execution_count": 18
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 3.安装必须的包"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "294db101eb6a780f"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: langchain-experimental in ./.venv/lib/python3.11/site-packages (0.3.3)\r\n",
      "Requirement already satisfied: langchain-community<0.4.0,>=0.3.0 in ./.venv/lib/python3.11/site-packages (from langchain-experimental) (0.3.5)\r\n",
      "Requirement already satisfied: langchain-core<0.4.0,>=0.3.15 in ./.venv/lib/python3.11/site-packages (from langchain-experimental) (0.3.15)\r\n",
      "Requirement already satisfied: PyYAML>=5.3 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (6.0.2)\r\n",
      "Requirement already satisfied: SQLAlchemy<2.0.36,>=1.4 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (2.0.35)\r\n",
      "Requirement already satisfied: aiohttp<4.0.0,>=3.8.3 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (3.10.10)\r\n",
      "Requirement already satisfied: dataclasses-json<0.7,>=0.5.7 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.6.7)\r\n",
      "Requirement already satisfied: httpx-sse<0.5.0,>=0.4.0 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.4.0)\r\n",
      "Requirement already satisfied: langchain<0.4.0,>=0.3.6 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.3.6)\r\n",
      "Requirement already satisfied: langsmith<0.2.0,>=0.1.125 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.1.138)\r\n",
      "Requirement already satisfied: numpy<2,>=1 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.26.4)\r\n",
      "Requirement already satisfied: pydantic-settings<3.0.0,>=2.4.0 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (2.6.1)\r\n",
      "Requirement already satisfied: requests<3,>=2 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (2.32.3)\r\n",
      "Requirement already satisfied: tenacity!=8.4.0,<10,>=8.1.0 in ./.venv/lib/python3.11/site-packages (from langchain-community<0.4.0,>=0.3.0->langchain-experimental) (9.0.0)\r\n",
      "Requirement already satisfied: jsonpatch<2.0,>=1.33 in ./.venv/lib/python3.11/site-packages (from langchain-core<0.4.0,>=0.3.15->langchain-experimental) (1.33)\r\n",
      "Requirement already satisfied: packaging<25,>=23.2 in ./.venv/lib/python3.11/site-packages (from langchain-core<0.4.0,>=0.3.15->langchain-experimental) (24.1)\r\n",
      "Requirement already satisfied: pydantic<3.0.0,>=2.5.2 in ./.venv/lib/python3.11/site-packages (from langchain-core<0.4.0,>=0.3.15->langchain-experimental) (2.9.2)\r\n",
      "Requirement already satisfied: typing-extensions>=4.7 in ./.venv/lib/python3.11/site-packages (from langchain-core<0.4.0,>=0.3.15->langchain-experimental) (4.12.2)\r\n",
      "Requirement already satisfied: aiohappyeyeballs>=2.3.0 in ./.venv/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (2.4.3)\r\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in ./.venv/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.3.1)\r\n",
      "Requirement already satisfied: attrs>=17.3.0 in ./.venv/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (24.2.0)\r\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in ./.venv/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.5.0)\r\n",
      "Requirement already satisfied: multidict<7.0,>=4.5 in ./.venv/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (6.1.0)\r\n",
      "Requirement already satisfied: yarl<2.0,>=1.12.0 in ./.venv/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.17.1)\r\n",
      "Requirement already satisfied: marshmallow<4.0.0,>=3.18.0 in ./.venv/lib/python3.11/site-packages (from dataclasses-json<0.7,>=0.5.7->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (3.23.1)\r\n",
      "Requirement already satisfied: typing-inspect<1,>=0.4.0 in ./.venv/lib/python3.11/site-packages (from dataclasses-json<0.7,>=0.5.7->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.9.0)\r\n",
      "Requirement already satisfied: jsonpointer>=1.9 in ./.venv/lib/python3.11/site-packages (from jsonpatch<2.0,>=1.33->langchain-core<0.4.0,>=0.3.15->langchain-experimental) (3.0.0)\r\n",
      "Requirement already satisfied: langchain-text-splitters<0.4.0,>=0.3.0 in ./.venv/lib/python3.11/site-packages (from langchain<0.4.0,>=0.3.6->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.3.1)\r\n",
      "Requirement already satisfied: httpx<1,>=0.23.0 in ./.venv/lib/python3.11/site-packages (from langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.27.2)\r\n",
      "Requirement already satisfied: orjson<4.0.0,>=3.9.14 in ./.venv/lib/python3.11/site-packages (from langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (3.10.10)\r\n",
      "Requirement already satisfied: requests-toolbelt<2.0.0,>=1.0.0 in ./.venv/lib/python3.11/site-packages (from langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.0.0)\r\n",
      "Requirement already satisfied: annotated-types>=0.6.0 in ./.venv/lib/python3.11/site-packages (from pydantic<3.0.0,>=2.5.2->langchain-core<0.4.0,>=0.3.15->langchain-experimental) (0.7.0)\r\n",
      "Requirement already satisfied: pydantic-core==2.23.4 in ./.venv/lib/python3.11/site-packages (from pydantic<3.0.0,>=2.5.2->langchain-core<0.4.0,>=0.3.15->langchain-experimental) (2.23.4)\r\n",
      "Requirement already satisfied: python-dotenv>=0.21.0 in ./.venv/lib/python3.11/site-packages (from pydantic-settings<3.0.0,>=2.4.0->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.0.1)\r\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in ./.venv/lib/python3.11/site-packages (from requests<3,>=2->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (3.4.0)\r\n",
      "Requirement already satisfied: idna<4,>=2.5 in ./.venv/lib/python3.11/site-packages (from requests<3,>=2->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (3.10)\r\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in ./.venv/lib/python3.11/site-packages (from requests<3,>=2->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (2.2.3)\r\n",
      "Requirement already satisfied: certifi>=2017.4.17 in ./.venv/lib/python3.11/site-packages (from requests<3,>=2->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (2024.8.30)\r\n",
      "Requirement already satisfied: greenlet!=0.4.17 in ./.venv/lib/python3.11/site-packages (from SQLAlchemy<2.0.36,>=1.4->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (3.1.1)\r\n",
      "Requirement already satisfied: anyio in ./.venv/lib/python3.11/site-packages (from httpx<1,>=0.23.0->langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (4.6.2.post1)\r\n",
      "Requirement already satisfied: httpcore==1.* in ./.venv/lib/python3.11/site-packages (from httpx<1,>=0.23.0->langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.0.6)\r\n",
      "Requirement already satisfied: sniffio in ./.venv/lib/python3.11/site-packages (from httpx<1,>=0.23.0->langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.3.1)\r\n",
      "Requirement already satisfied: h11<0.15,>=0.13 in ./.venv/lib/python3.11/site-packages (from httpcore==1.*->httpx<1,>=0.23.0->langsmith<0.2.0,>=0.1.125->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.14.0)\r\n",
      "Requirement already satisfied: mypy-extensions>=0.3.0 in ./.venv/lib/python3.11/site-packages (from typing-inspect<1,>=0.4.0->dataclasses-json<0.7,>=0.5.7->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (1.0.0)\r\n",
      "Requirement already satisfied: propcache>=0.2.0 in ./.venv/lib/python3.11/site-packages (from yarl<2.0,>=1.12.0->aiohttp<4.0.0,>=3.8.3->langchain-community<0.4.0,>=0.3.0->langchain-experimental) (0.2.0)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install langchain-experimental"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:08:20.972185Z",
     "start_time": "2024-11-08T06:08:17.658731Z"
    }
   },
   "id": "ef94fc5ffae3e64f",
   "execution_count": 19
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 4.准备数据库\n",
    "### https://database.guide/2-sample-databases-sqlite/\n",
    "### 现在创建一个名为 Chinook 的数据库。您可以使用以下命令连接到 SQLite 来执行此操作：\n",
    "#### sqlite3 Chinook.db\n",
    "### 现在您可以运行该脚本。要从文件运行它，请使用以下命令：\n",
    "#### .read Chinook_Sqlite.sql"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "608ad5bd38cd9f4e"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 5.询问简单的问题"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ec245e6345d0be75"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001B[1m> Entering new SQLDatabaseChain chain...\u001B[0m\n",
      "总共有多少员工?\n",
      "SQLQuery:\u001B[32;1m\u001B[1;3mSQLQuery: SELECT COUNT(*) FROM \"Employee\"\u001B[0m\n",
      "SQLResult: \u001B[33;1m\u001B[1;3m[(8,)]\u001B[0m\n",
      "Answer:\u001B[32;1m\u001B[1;3m总共有8名员工。\u001B[0m\n",
      "\u001B[1m> Finished chain.\u001B[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": "{'query': '总共有多少员工?', 'result': '总共有8名员工。'}"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from langchain.utilities import SQLDatabase\n",
    "from langchain_experimental.sql import SQLDatabaseChain\n",
    "\n",
    "db = SQLDatabase.from_uri(\"sqlite:///./db/Chinook.db\")\n",
    "db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)\n",
    "\n",
    "db_chain.invoke(\"总共有多少员工?\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:08:24.862102Z",
     "start_time": "2024-11-08T06:08:20.975273Z"
    }
   },
   "id": "df24fa8d2caed5ba",
   "execution_count": 20
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 6.使用英文询问简单的问题"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "5a38789057bdd428"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001B[1m> Entering new SQLDatabaseChain chain...\u001B[0m\n",
      "how many employees are there?\n",
      "SQLQuery:\u001B[32;1m\u001B[1;3mSQLQuery: SELECT COUNT(*) FROM \"Employee\"\u001B[0m\n",
      "SQLResult: \u001B[33;1m\u001B[1;3m[(8,)]\u001B[0m\n",
      "Answer:\u001B[32;1m\u001B[1;3mThere are 8 employees.\u001B[0m\n",
      "\u001B[1m> Finished chain.\u001B[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": "{'query': 'how many employees are there?', 'result': 'There are 8 employees.'}"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_chain.invoke(\"how many employees are there?\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:08:28.295598Z",
     "start_time": "2024-11-08T06:08:24.880464Z"
    }
   },
   "id": "2d3778f4c21ae60d",
   "execution_count": 21
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 7.问一个复杂的问题\n",
    "#### 面对这样的结果我们应该如何优化？"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "8014601afc96d8fa"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001B[1m> Entering new SQLDatabaseChain chain...\u001B[0m\n",
      "连续两个月都下订单的客户有哪些？\n",
      "SQLQuery:\u001B[32;1m\u001B[1;3m为了找出连续两个月都下订单的客户，我们需要首先找出每个客户的订单日期，并检查是否有连续两个月的订单。这需要一些复杂的SQL查询，包括子查询和日期操作。这里我们使用窗口函数和日期差来实现这个目标。\n",
      "\n",
      "首先，我们需要找出每个客户的最早和最晚的订单日期，然后检查是否有连续两个月的订单。这里我们假设连续两个月的订单日期差小于等于31天。\n",
      "\n",
      "SQLQuery:\n",
      "```sql\n",
      "WITH CustomerOrders AS (\n",
      "    SELECT \n",
      "        CustomerId,\n",
      "        MIN(InvoiceDate) AS MinDate,\n",
      "        MAX(InvoiceDate) AS MaxDate\n",
      "    FROM Invoice\n",
      "    GROUP BY CustomerId\n",
      "),\n",
      "DateGaps AS (\n",
      "    SELECT \n",
      "        CO.CustomerId,\n",
      "        CO.MinDate,\n",
      "        CO.MaxDate,\n",
      "        DATEADD('day', -31, CO.MaxDate) AS GapDate\n",
      "    FROM CustomerOrders CO\n",
      "),\n",
      "InvoiceDates AS (\n",
      "    SELECT \n",
      "        CustomerId,\n",
      "        InvoiceDate\n",
      "    FROM Invoice\n",
      "),\n",
      "Gaps AS (\n",
      "    SELECT \n",
      "        DG.CustomerId,\n",
      "        DG.MinDate,\n",
      "        DG.MaxDate,\n",
      "        ID.InvoiceDate\n",
      "    FROM DateGaps DG\n",
      "    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n",
      "    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n",
      ")\n",
      "SELECT DISTINCT \n",
      "    C.CustomerId,\n",
      "    C.FirstName,\n",
      "    C.LastName\n",
      "FROM Gaps G\n",
      "JOIN Customer C ON C.CustomerId = G.CustomerId\n",
      "WHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n",
      "       FROM Invoice \n",
      "       WHERE CustomerId = G.CustomerId) > 1\n",
      "LIMIT 5;\n",
      "```\n",
      "\n",
      "注意：上述查询使用了`DATEADD`函数，这是SQL Server的函数，而不是SQLite的。在SQLite中，我们需要使用`date('now', '+31 days')`这样的语法来添加天数。但是，为了简化查询，这里我们直接使用了`DATEADD`函数。在实际的SQLite查询中，你需要将`DATEADD('day', -31, CO.MaxDate)`替换为`date(CO.MaxDate, '-31 days')`。\n",
      "\n",
      "SQLQuery (修正后的SQLite版本):\n",
      "```sql\n",
      "WITH CustomerOrders AS (\n",
      "    SELECT \n",
      "        CustomerId,\n",
      "        MIN(InvoiceDate) AS MinDate,\n",
      "        MAX(InvoiceDate) AS MaxDate\n",
      "    FROM Invoice\n",
      "    GROUP BY CustomerId\n",
      "),\n",
      "DateGaps AS (\n",
      "    SELECT \n",
      "        CO.CustomerId,\n",
      "        CO.MinDate,\n",
      "        CO.MaxDate,\n",
      "        date(CO.MaxDate, '-31 days') AS GapDate\n",
      "    FROM CustomerOrders CO\n",
      "),\n",
      "InvoiceDates AS (\n",
      "    SELECT \n",
      "        CustomerId,\n",
      "        InvoiceDate\n",
      "    FROM Invoice\n",
      "),\n",
      "Gaps AS (\n",
      "    SELECT \n",
      "        DG.CustomerId,\n",
      "        DG.MinDate,\n",
      "        DG.MaxDate,\n",
      "        ID.InvoiceDate\n",
      "    FROM DateGaps DG\n",
      "    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n",
      "    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n",
      ")\n",
      "SELECT DISTINCT \n",
      "    C.CustomerId,\n",
      "    C.\"FirstName\",\n",
      "    C.\"LastName\"\n",
      "FROM Gaps G\n",
      "JOIN Customer C ON C.CustomerId = G.CustomerId\n",
      "WHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n",
      "       FROM Invoice \n",
      "       WHERE CustomerId = G.CustomerId) > 1\n",
      "LIMIT 5;\n",
      "```\u001B[0m"
     ]
    },
    {
     "ename": "OperationalError",
     "evalue": "(sqlite3.OperationalError) near \"```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        DATEADD('day', -31, CO.MaxDate) AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.FirstName,\n    C.LastName\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```\": syntax error\n[SQL: ```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        DATEADD('day', -31, CO.MaxDate) AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.FirstName,\n    C.LastName\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```\n\n注意：上述查询使用了`DATEADD`函数，这是SQL Server的函数，而不是SQLite的。在SQLite中，我们需要使用`date('now', '+31 days')`这样的语法来添加天数。但是，为了简化查询，这里我们直接使用了`DATEADD`函数。在实际的SQLite查询中，你需要将`DATEADD('day', -31, CO.MaxDate)`替换为`date(CO.MaxDate, '-31 days')`。\n\nSQLQuery (修正后的SQLite版本):\n```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        date(CO.MaxDate, '-31 days') AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.\"FirstName\",\n    C.\"LastName\"\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001B[0;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[0;31mOperationalError\u001B[0m                          Traceback (most recent call last)",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1967\u001B[0m, in \u001B[0;36mConnection._exec_single_context\u001B[0;34m(self, dialect, context, statement, parameters)\u001B[0m\n\u001B[1;32m   1966\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m evt_handled:\n\u001B[0;32m-> 1967\u001B[0m         \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mdialect\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mdo_execute\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1968\u001B[0m \u001B[43m            \u001B[49m\u001B[43mcursor\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mstr_statement\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43meffective_parameters\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mcontext\u001B[49m\n\u001B[1;32m   1969\u001B[0m \u001B[43m        \u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m   1971\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_has_events \u001B[38;5;129;01mor\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mengine\u001B[38;5;241m.\u001B[39m_has_events:\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py:941\u001B[0m, in \u001B[0;36mDefaultDialect.do_execute\u001B[0;34m(self, cursor, statement, parameters, context)\u001B[0m\n\u001B[1;32m    940\u001B[0m \u001B[38;5;28;01mdef\u001B[39;00m \u001B[38;5;21mdo_execute\u001B[39m(\u001B[38;5;28mself\u001B[39m, cursor, statement, parameters, context\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mNone\u001B[39;00m):\n\u001B[0;32m--> 941\u001B[0m     cursor\u001B[38;5;241m.\u001B[39mexecute(statement, parameters)\n",
      "\u001B[0;31mOperationalError\u001B[0m: near \"```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        DATEADD('day', -31, CO.MaxDate) AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.FirstName,\n    C.LastName\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```\": syntax error",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001B[0;31mOperationalError\u001B[0m                          Traceback (most recent call last)",
      "Cell \u001B[0;32mIn[22], line 1\u001B[0m\n\u001B[0;32m----> 1\u001B[0m \u001B[43mdb_chain\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43minvoke\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43m连续两个月都下订单的客户有哪些？\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m)\u001B[49m\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/langchain/chains/base.py:170\u001B[0m, in \u001B[0;36mChain.invoke\u001B[0;34m(self, input, config, **kwargs)\u001B[0m\n\u001B[1;32m    168\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mBaseException\u001B[39;00m \u001B[38;5;28;01mas\u001B[39;00m e:\n\u001B[1;32m    169\u001B[0m     run_manager\u001B[38;5;241m.\u001B[39mon_chain_error(e)\n\u001B[0;32m--> 170\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m e\n\u001B[1;32m    171\u001B[0m run_manager\u001B[38;5;241m.\u001B[39mon_chain_end(outputs)\n\u001B[1;32m    173\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m include_run_info:\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/langchain/chains/base.py:160\u001B[0m, in \u001B[0;36mChain.invoke\u001B[0;34m(self, input, config, **kwargs)\u001B[0m\n\u001B[1;32m    157\u001B[0m \u001B[38;5;28;01mtry\u001B[39;00m:\n\u001B[1;32m    158\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_validate_inputs(inputs)\n\u001B[1;32m    159\u001B[0m     outputs \u001B[38;5;241m=\u001B[39m (\n\u001B[0;32m--> 160\u001B[0m         \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_call\u001B[49m\u001B[43m(\u001B[49m\u001B[43minputs\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mrun_manager\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mrun_manager\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    161\u001B[0m         \u001B[38;5;28;01mif\u001B[39;00m new_arg_supported\n\u001B[1;32m    162\u001B[0m         \u001B[38;5;28;01melse\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_call(inputs)\n\u001B[1;32m    163\u001B[0m     )\n\u001B[1;32m    165\u001B[0m     final_outputs: Dict[\u001B[38;5;28mstr\u001B[39m, Any] \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mprep_outputs(\n\u001B[1;32m    166\u001B[0m         inputs, outputs, return_only_outputs\n\u001B[1;32m    167\u001B[0m     )\n\u001B[1;32m    168\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mBaseException\u001B[39;00m \u001B[38;5;28;01mas\u001B[39;00m e:\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/langchain_experimental/sql/base.py:204\u001B[0m, in \u001B[0;36mSQLDatabaseChain._call\u001B[0;34m(self, inputs, run_manager)\u001B[0m\n\u001B[1;32m    200\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mException\u001B[39;00m \u001B[38;5;28;01mas\u001B[39;00m exc:\n\u001B[1;32m    201\u001B[0m     \u001B[38;5;66;03m# Append intermediate steps to exception, to aid in logging and later\u001B[39;00m\n\u001B[1;32m    202\u001B[0m     \u001B[38;5;66;03m# improvement of few shot prompt seeds\u001B[39;00m\n\u001B[1;32m    203\u001B[0m     exc\u001B[38;5;241m.\u001B[39mintermediate_steps \u001B[38;5;241m=\u001B[39m intermediate_steps  \u001B[38;5;66;03m# type: ignore\u001B[39;00m\n\u001B[0;32m--> 204\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m exc\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/langchain_experimental/sql/base.py:149\u001B[0m, in \u001B[0;36mSQLDatabaseChain._call\u001B[0;34m(self, inputs, run_manager)\u001B[0m\n\u001B[1;32m    147\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m SQL_RESULT \u001B[38;5;129;01min\u001B[39;00m sql_cmd:\n\u001B[1;32m    148\u001B[0m         sql_cmd \u001B[38;5;241m=\u001B[39m sql_cmd\u001B[38;5;241m.\u001B[39msplit(SQL_RESULT)[\u001B[38;5;241m0\u001B[39m]\u001B[38;5;241m.\u001B[39mstrip()\n\u001B[0;32m--> 149\u001B[0m     result \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mdatabase\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mrun\u001B[49m\u001B[43m(\u001B[49m\u001B[43msql_cmd\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    150\u001B[0m     intermediate_steps\u001B[38;5;241m.\u001B[39mappend(\u001B[38;5;28mstr\u001B[39m(result))  \u001B[38;5;66;03m# output: sql exec\u001B[39;00m\n\u001B[1;32m    151\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/langchain_community/utilities/sql_database.py:502\u001B[0m, in \u001B[0;36mSQLDatabase.run\u001B[0;34m(self, command, fetch, include_columns, parameters, execution_options)\u001B[0m\n\u001B[1;32m    488\u001B[0m \u001B[38;5;28;01mdef\u001B[39;00m \u001B[38;5;21mrun\u001B[39m(\n\u001B[1;32m    489\u001B[0m     \u001B[38;5;28mself\u001B[39m,\n\u001B[1;32m    490\u001B[0m     command: Union[\u001B[38;5;28mstr\u001B[39m, Executable],\n\u001B[0;32m   (...)\u001B[0m\n\u001B[1;32m    495\u001B[0m     execution_options: Optional[Dict[\u001B[38;5;28mstr\u001B[39m, Any]] \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;01mNone\u001B[39;00m,\n\u001B[1;32m    496\u001B[0m ) \u001B[38;5;241m-\u001B[39m\u001B[38;5;241m>\u001B[39m Union[\u001B[38;5;28mstr\u001B[39m, Sequence[Dict[\u001B[38;5;28mstr\u001B[39m, Any]], Result[Any]]:\n\u001B[1;32m    497\u001B[0m \u001B[38;5;250m    \u001B[39m\u001B[38;5;124;03m\"\"\"Execute a SQL command and return a string representing the results.\u001B[39;00m\n\u001B[1;32m    498\u001B[0m \n\u001B[1;32m    499\u001B[0m \u001B[38;5;124;03m    If the statement returns rows, a string of the results is returned.\u001B[39;00m\n\u001B[1;32m    500\u001B[0m \u001B[38;5;124;03m    If the statement returns no rows, an empty string is returned.\u001B[39;00m\n\u001B[1;32m    501\u001B[0m \u001B[38;5;124;03m    \"\"\"\u001B[39;00m\n\u001B[0;32m--> 502\u001B[0m     result \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_execute\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m    503\u001B[0m \u001B[43m        \u001B[49m\u001B[43mcommand\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mfetch\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mparameters\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mparameters\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mexecution_options\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mexecution_options\u001B[49m\n\u001B[1;32m    504\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    506\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m fetch \u001B[38;5;241m==\u001B[39m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mcursor\u001B[39m\u001B[38;5;124m\"\u001B[39m:\n\u001B[1;32m    507\u001B[0m         \u001B[38;5;28;01mreturn\u001B[39;00m result\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/langchain_community/utilities/sql_database.py:467\u001B[0m, in \u001B[0;36mSQLDatabase._execute\u001B[0;34m(self, command, fetch, parameters, execution_options)\u001B[0m\n\u001B[1;32m    465\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m    466\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mTypeError\u001B[39;00m(\u001B[38;5;124mf\u001B[39m\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mQuery expression has unknown type: \u001B[39m\u001B[38;5;132;01m{\u001B[39;00m\u001B[38;5;28mtype\u001B[39m(command)\u001B[38;5;132;01m}\u001B[39;00m\u001B[38;5;124m\"\u001B[39m)\n\u001B[0;32m--> 467\u001B[0m cursor \u001B[38;5;241m=\u001B[39m \u001B[43mconnection\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mexecute\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m    468\u001B[0m \u001B[43m    \u001B[49m\u001B[43mcommand\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    469\u001B[0m \u001B[43m    \u001B[49m\u001B[43mparameters\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    470\u001B[0m \u001B[43m    \u001B[49m\u001B[43mexecution_options\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mexecution_options\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m    471\u001B[0m \u001B[43m\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    473\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m cursor\u001B[38;5;241m.\u001B[39mreturns_rows:\n\u001B[1;32m    474\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m fetch \u001B[38;5;241m==\u001B[39m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mall\u001B[39m\u001B[38;5;124m\"\u001B[39m:\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1418\u001B[0m, in \u001B[0;36mConnection.execute\u001B[0;34m(self, statement, parameters, execution_options)\u001B[0m\n\u001B[1;32m   1416\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m exc\u001B[38;5;241m.\u001B[39mObjectNotExecutableError(statement) \u001B[38;5;28;01mfrom\u001B[39;00m \u001B[38;5;21;01merr\u001B[39;00m\n\u001B[1;32m   1417\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m-> 1418\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[43mmeth\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1419\u001B[0m \u001B[43m        \u001B[49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1420\u001B[0m \u001B[43m        \u001B[49m\u001B[43mdistilled_parameters\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1421\u001B[0m \u001B[43m        \u001B[49m\u001B[43mexecution_options\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;129;43;01mor\u001B[39;49;00m\u001B[43m \u001B[49m\u001B[43mNO_OPTIONS\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1422\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:515\u001B[0m, in \u001B[0;36mClauseElement._execute_on_connection\u001B[0;34m(self, connection, distilled_params, execution_options)\u001B[0m\n\u001B[1;32m    513\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m TYPE_CHECKING:\n\u001B[1;32m    514\u001B[0m         \u001B[38;5;28;01massert\u001B[39;00m \u001B[38;5;28misinstance\u001B[39m(\u001B[38;5;28mself\u001B[39m, Executable)\n\u001B[0;32m--> 515\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[43mconnection\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_execute_clauseelement\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m    516\u001B[0m \u001B[43m        \u001B[49m\u001B[38;5;28;43mself\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mdistilled_params\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mexecution_options\u001B[49m\n\u001B[1;32m    517\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m    518\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m    519\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m exc\u001B[38;5;241m.\u001B[39mObjectNotExecutableError(\u001B[38;5;28mself\u001B[39m)\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1640\u001B[0m, in \u001B[0;36mConnection._execute_clauseelement\u001B[0;34m(self, elem, distilled_parameters, execution_options)\u001B[0m\n\u001B[1;32m   1628\u001B[0m compiled_cache: Optional[CompiledCacheType] \u001B[38;5;241m=\u001B[39m execution_options\u001B[38;5;241m.\u001B[39mget(\n\u001B[1;32m   1629\u001B[0m     \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mcompiled_cache\u001B[39m\u001B[38;5;124m\"\u001B[39m, \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mengine\u001B[38;5;241m.\u001B[39m_compiled_cache\n\u001B[1;32m   1630\u001B[0m )\n\u001B[1;32m   1632\u001B[0m compiled_sql, extracted_params, cache_hit \u001B[38;5;241m=\u001B[39m elem\u001B[38;5;241m.\u001B[39m_compile_w_cache(\n\u001B[1;32m   1633\u001B[0m     dialect\u001B[38;5;241m=\u001B[39mdialect,\n\u001B[1;32m   1634\u001B[0m     compiled_cache\u001B[38;5;241m=\u001B[39mcompiled_cache,\n\u001B[0;32m   (...)\u001B[0m\n\u001B[1;32m   1638\u001B[0m     linting\u001B[38;5;241m=\u001B[39m\u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mdialect\u001B[38;5;241m.\u001B[39mcompiler_linting \u001B[38;5;241m|\u001B[39m compiler\u001B[38;5;241m.\u001B[39mWARN_LINTING,\n\u001B[1;32m   1639\u001B[0m )\n\u001B[0;32m-> 1640\u001B[0m ret \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_execute_context\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1641\u001B[0m \u001B[43m    \u001B[49m\u001B[43mdialect\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1642\u001B[0m \u001B[43m    \u001B[49m\u001B[43mdialect\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mexecution_ctx_cls\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_init_compiled\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1643\u001B[0m \u001B[43m    \u001B[49m\u001B[43mcompiled_sql\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1644\u001B[0m \u001B[43m    \u001B[49m\u001B[43mdistilled_parameters\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1645\u001B[0m \u001B[43m    \u001B[49m\u001B[43mexecution_options\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1646\u001B[0m \u001B[43m    \u001B[49m\u001B[43mcompiled_sql\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1647\u001B[0m \u001B[43m    \u001B[49m\u001B[43mdistilled_parameters\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1648\u001B[0m \u001B[43m    \u001B[49m\u001B[43melem\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1649\u001B[0m \u001B[43m    \u001B[49m\u001B[43mextracted_params\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1650\u001B[0m \u001B[43m    \u001B[49m\u001B[43mcache_hit\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mcache_hit\u001B[49m\u001B[43m,\u001B[49m\n\u001B[1;32m   1651\u001B[0m \u001B[43m\u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m   1652\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m has_events:\n\u001B[1;32m   1653\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mdispatch\u001B[38;5;241m.\u001B[39mafter_execute(\n\u001B[1;32m   1654\u001B[0m         \u001B[38;5;28mself\u001B[39m,\n\u001B[1;32m   1655\u001B[0m         elem,\n\u001B[0;32m   (...)\u001B[0m\n\u001B[1;32m   1659\u001B[0m         ret,\n\u001B[1;32m   1660\u001B[0m     )\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1846\u001B[0m, in \u001B[0;36mConnection._execute_context\u001B[0;34m(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)\u001B[0m\n\u001B[1;32m   1844\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_exec_insertmany_context(dialect, context)\n\u001B[1;32m   1845\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m-> 1846\u001B[0m     \u001B[38;5;28;01mreturn\u001B[39;00m \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_exec_single_context\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1847\u001B[0m \u001B[43m        \u001B[49m\u001B[43mdialect\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mcontext\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mstatement\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mparameters\u001B[49m\n\u001B[1;32m   1848\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1986\u001B[0m, in \u001B[0;36mConnection._exec_single_context\u001B[0;34m(self, dialect, context, statement, parameters)\u001B[0m\n\u001B[1;32m   1983\u001B[0m     result \u001B[38;5;241m=\u001B[39m context\u001B[38;5;241m.\u001B[39m_setup_result_proxy()\n\u001B[1;32m   1985\u001B[0m \u001B[38;5;28;01mexcept\u001B[39;00m \u001B[38;5;167;01mBaseException\u001B[39;00m \u001B[38;5;28;01mas\u001B[39;00m e:\n\u001B[0;32m-> 1986\u001B[0m     \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43m_handle_dbapi_exception\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1987\u001B[0m \u001B[43m        \u001B[49m\u001B[43me\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mstr_statement\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43meffective_parameters\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mcursor\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mcontext\u001B[49m\n\u001B[1;32m   1988\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m   1990\u001B[0m \u001B[38;5;28;01mreturn\u001B[39;00m result\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2355\u001B[0m, in \u001B[0;36mConnection._handle_dbapi_exception\u001B[0;34m(self, e, statement, parameters, cursor, context, is_sub_exec)\u001B[0m\n\u001B[1;32m   2353\u001B[0m \u001B[38;5;28;01melif\u001B[39;00m should_wrap:\n\u001B[1;32m   2354\u001B[0m     \u001B[38;5;28;01massert\u001B[39;00m sqlalchemy_exception \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m\n\u001B[0;32m-> 2355\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m sqlalchemy_exception\u001B[38;5;241m.\u001B[39mwith_traceback(exc_info[\u001B[38;5;241m2\u001B[39m]) \u001B[38;5;28;01mfrom\u001B[39;00m \u001B[38;5;21;01me\u001B[39;00m\n\u001B[1;32m   2356\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m   2357\u001B[0m     \u001B[38;5;28;01massert\u001B[39;00m exc_info[\u001B[38;5;241m1\u001B[39m] \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1967\u001B[0m, in \u001B[0;36mConnection._exec_single_context\u001B[0;34m(self, dialect, context, statement, parameters)\u001B[0m\n\u001B[1;32m   1965\u001B[0m                 \u001B[38;5;28;01mbreak\u001B[39;00m\n\u001B[1;32m   1966\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m evt_handled:\n\u001B[0;32m-> 1967\u001B[0m         \u001B[38;5;28;43mself\u001B[39;49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mdialect\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mdo_execute\u001B[49m\u001B[43m(\u001B[49m\n\u001B[1;32m   1968\u001B[0m \u001B[43m            \u001B[49m\u001B[43mcursor\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mstr_statement\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43meffective_parameters\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mcontext\u001B[49m\n\u001B[1;32m   1969\u001B[0m \u001B[43m        \u001B[49m\u001B[43m)\u001B[49m\n\u001B[1;32m   1971\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39m_has_events \u001B[38;5;129;01mor\u001B[39;00m \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mengine\u001B[38;5;241m.\u001B[39m_has_events:\n\u001B[1;32m   1972\u001B[0m     \u001B[38;5;28mself\u001B[39m\u001B[38;5;241m.\u001B[39mdispatch\u001B[38;5;241m.\u001B[39mafter_cursor_execute(\n\u001B[1;32m   1973\u001B[0m         \u001B[38;5;28mself\u001B[39m,\n\u001B[1;32m   1974\u001B[0m         cursor,\n\u001B[0;32m   (...)\u001B[0m\n\u001B[1;32m   1978\u001B[0m         context\u001B[38;5;241m.\u001B[39mexecutemany,\n\u001B[1;32m   1979\u001B[0m     )\n",
      "File \u001B[0;32m~/code/langchainv3/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py:941\u001B[0m, in \u001B[0;36mDefaultDialect.do_execute\u001B[0;34m(self, cursor, statement, parameters, context)\u001B[0m\n\u001B[1;32m    940\u001B[0m \u001B[38;5;28;01mdef\u001B[39;00m \u001B[38;5;21mdo_execute\u001B[39m(\u001B[38;5;28mself\u001B[39m, cursor, statement, parameters, context\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mNone\u001B[39;00m):\n\u001B[0;32m--> 941\u001B[0m     cursor\u001B[38;5;241m.\u001B[39mexecute(statement, parameters)\n",
      "\u001B[0;31mOperationalError\u001B[0m: (sqlite3.OperationalError) near \"```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        DATEADD('day', -31, CO.MaxDate) AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.FirstName,\n    C.LastName\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```\": syntax error\n[SQL: ```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        DATEADD('day', -31, CO.MaxDate) AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.FirstName,\n    C.LastName\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```\n\n注意：上述查询使用了`DATEADD`函数，这是SQL Server的函数，而不是SQLite的。在SQLite中，我们需要使用`date('now', '+31 days')`这样的语法来添加天数。但是，为了简化查询，这里我们直接使用了`DATEADD`函数。在实际的SQLite查询中，你需要将`DATEADD('day', -31, CO.MaxDate)`替换为`date(CO.MaxDate, '-31 days')`。\n\nSQLQuery (修正后的SQLite版本):\n```sql\nWITH CustomerOrders AS (\n    SELECT \n        CustomerId,\n        MIN(InvoiceDate) AS MinDate,\n        MAX(InvoiceDate) AS MaxDate\n    FROM Invoice\n    GROUP BY CustomerId\n),\nDateGaps AS (\n    SELECT \n        CO.CustomerId,\n        CO.MinDate,\n        CO.MaxDate,\n        date(CO.MaxDate, '-31 days') AS GapDate\n    FROM CustomerOrders CO\n),\nInvoiceDates AS (\n    SELECT \n        CustomerId,\n        InvoiceDate\n    FROM Invoice\n),\nGaps AS (\n    SELECT \n        DG.CustomerId,\n        DG.MinDate,\n        DG.MaxDate,\n        ID.InvoiceDate\n    FROM DateGaps DG\n    JOIN InvoiceDates ID ON ID.CustomerId = DG.CustomerId\n    WHERE ID.InvoiceDate BETWEEN DG.MinDate AND DG.GapDate\n)\nSELECT DISTINCT \n    C.CustomerId,\n    C.\"FirstName\",\n    C.\"LastName\"\nFROM Gaps G\nJOIN Customer C ON C.CustomerId = G.CustomerId\nWHERE (SELECT COUNT(DISTINCT strftime('%Y-%m', InvoiceDate)) \n       FROM Invoice \n       WHERE CustomerId = G.CustomerId) > 1\nLIMIT 5;\n```]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)"
     ]
    }
   ],
   "source": [
    "db_chain.invoke(\"连续两个月都下订单的客户有哪些？\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:09:00.203709Z",
     "start_time": "2024-11-08T06:08:28.298231Z"
    }
   },
   "id": "dad520f1f235bae6",
   "execution_count": 22
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 8.添加提示词和输出来优化sql的生成"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ca47da92121d2334"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "from langchain.schema.output_parser import StrOutputParser\n",
    "from langchain import hub\n",
    "\n",
    "prompt = hub.pull(\"rlm/text-to-sql\", api_key=langsmith_api_key)\n",
    "\n",
    "few_shots = {\n",
    "    \"List all artists.\": \"SELECT * FROM artists;\",\n",
    "    \"Find all albums for the artist 'AC/DC'.\": \"SELECT * FROM albums WHERE ArtistId = (SELECT ArtistId FROM artists WHERE Name = 'AC/DC');\",\n",
    "    \"连续两个月都下订单的客户有哪些?\":\"SELECT DISTINCT a.'CustomerId', a.'FirstName', a.'LastName' FROM 'Customer' a JOIN 'Invoice' b ON a.'CustomerId' = b.'CustomerId' JOIN 'Invoice' c ON a.'CustomerId' = c.'CustomerId' AND ((strftime('%Y-%m', b.'InvoiceDate') = strftime('%Y-%m', date(c.'InvoiceDate', '-1 month'))) OR (strftime('%Y-%m', b.'InvoiceDate') = strftime('%Y-%m', date(c.'InvoiceDate', '+1 month'))))\"\n",
    "}\n",
    "\n",
    "inputs = {\n",
    "    \"table_info\": lambda x: db.get_table_info(),\n",
    "    \"input\": lambda x: x[\"question\"],\n",
    "    \"few_shot_examples\": lambda x: few_shots,\n",
    "    \"dialect\": lambda x: db.dialect,\n",
    "}\n",
    "\n",
    "sql_response = (\n",
    "    inputs\n",
    "    | prompt\n",
    "    | llm.bind(stop=[\"\\nSQLResult:\"])\n",
    "    | StrOutputParser()\n",
    ")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:09:00.210947Z",
     "start_time": "2024-11-08T06:09:00.210428Z"
    }
   },
   "id": "bae24a1abd911be",
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 测试案例1:"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "6f5541d2cb1a7ec0"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "'SQLQuery: \"SELECT DISTINCT a.CustomerId, a.FirstName, a.LastName FROM Customer a JOIN Invoice b ON a.CustomerId = b.CustomerId JOIN Invoice c ON a.CustomerId = c.CustomerId AND ((strftime(\\'%Y-%m\\', b.InvoiceDate) = strftime(\\'%Y-%m\\', date(c.InvoiceDate, \\'-1 month\\'))) OR (strftime(\\'%Y-%m\\', b.InvoiceDate) = strftime(\\'%Y-%m\\', date(c.InvoiceDate, \\'+1 month\\'))))\"'"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_response.invoke({\"question\": \"连续两个月都下订单的客户有哪些?\"})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:13:06.313612Z",
     "start_time": "2024-11-08T06:13:00.888710Z"
    }
   },
   "id": "d07d0050925ba851",
   "execution_count": 23
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### 测试案例2:"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "b915ef95eaa9212c"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "'SQLQuery: \"SELECT COUNT(DISTINCT a.CustomerId) AS OrderCount FROM Invoice a JOIN Invoice b ON a.CustomerId = b.CustomerId AND ((strftime(\\'%Y-%m\\', a.InvoiceDate) = strftime(\\'%Y-%m\\', date(b.InvoiceDate, \\'-1 month\\'))) OR (strftime(\\'%Y-%m\\', a.InvoiceDate) = strftime(\\'%Y-%m\\', date(b.InvoiceDate, \\'+1 month\\')))) AND a.InvoiceId != b.InvoiceId;\"'"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_response.invoke({\"question\": \"连续两个月都下订单的客户的订单总共有多少个？\"})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-11-08T06:13:11.498796Z",
     "start_time": "2024-11-08T06:13:06.317081Z"
    }
   },
   "id": "8c0ea355ba3c1fcd",
   "execution_count": 24
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
